//
// Copyright (c) 2014, 保留所有权利。
//
// TradeSync.java
//
// 作    者：王红燕
// 当前版本：1.0
// 2014 下午1:15:59
//
// 
//
package com.vanke.agent.sync;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.log4j.Logger;

import com.vanke.agent.ADataSource;
import com.vanke.agent.C;
import com.vanke.agent.bean.ProductOrder;
import com.vanke.agent.common.CstUtil;
import com.vanke.agent.common.log.LoggerUtil;

/**
 * 房屋信息同步
 */
public class RoomSync extends AgentSync {
	Logger log = Logger.getLogger(RoomSync.class);
	Logger productOrderLog = LoggerUtil.getLoggerByName("product_order");

	String table = "p_Room";
	String field = C.FIELDS_ROOM;
	List<ProductOrder> orders = new ArrayList<ProductOrder>(6000);

	Map<String, Set<Long>> oldRoomCstGUIDMapping = new HashMap<String, Set<Long>>();

	@Override
	public void sync() {
		log.info("=============sync new p_Room===========");
		List<String> tables = loadProductOrderTables();
		for (String table : tables) {
			cacheOldOrders(table);
		}
		log.info("total " + oldRoomCstGUIDMapping.size() + " old order cached");
		Connection myCon = ADataSource.getConnection(ADataSource.SCHEMA_MY);
		Connection bizCon = ADataSource.getConnection(ADataSource.SCHEMA_BIZ);
		PreparedStatement dataps = null;
		ResultSet datars = null;

		PreparedStatement insertPstmt = null;
		PreparedStatement updatePstmt = null;

		PreparedStatement queryPstmt = null;
		ResultSet queryRs = null;
		try {
			dataps = bizCon.prepareStatement("select RoomGUID from p_Room",
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			datars = dataps.executeQuery();
			Map<String, Boolean> dataGuidMap = new HashMap<String, Boolean>();
			while (datars.next()) {
				dataGuidMap.put(datars.getString("RoomGUID").trim(), true);
			}

			String querySql = "SELECT " + field + "  FROM " + table;
			log.info("execute: " + querySql);
			queryPstmt = myCon.prepareStatement(querySql);
			queryRs = queryPstmt.executeQuery();
			int count = 0;
			int updateFlag = 0;
			if (!queryRs.next()) {
				log.info("no p_Room is created");
				ADataSource.close(queryRs);
				ADataSource.close(queryPstmt);
				return;
			}

			String updateFields = "RoomGUID,BUGUID,ProjGUID,BldGUID,MainRoomGUID,CstName,CstGUIDList,Unit,Floor,No,Room,RoomCode,HuXing,Status,BldArea,TnArea,BlRhDate,YFBZ,RoomMemo,ZxBz,Price,TnPrice,ZxPrice,ZxTotal,BedeckKind,IsTempletRoom,West,ChooseRoom,ChooseRoomDate,RoomStru,AbsolutelyFloor,StatusChgGUID,SaleRentable,BProductTypeCode,HtRoom,DxsArea,CarArea,LtArea,FpDate,ZdFpDate,CqfzNum,YsBldArea,ScBldArea,ScTnArea,YsTnArea,HyArea,FlatArea,YtArea,RhhyArea,YsxkzDate,YsxkzNo,CqfzGetDate,YyjfDate,IsCannotSale,CannotSaleReason,ChooseRoomNo,Variety,ProductAttribute,ParkingType,ParkingCurrentHandleMethod,ParkingSaleType,ParkingHandleDate,IsParkingCq,BusinessCqLife,RemarkHTML,BusinessManageFee,BuysRoomNo,ParkingPattern,ChooseRoomCstGUID,ProductFormCode,ProductFormName";
			String insertFields = updateFields + ",CreatedOn";

			String insertSql = CstUtil.formatInsertSql(table, insertFields);
			insertPstmt = bizCon.prepareStatement(insertSql);

			String updateSql = CstUtil.formatUpdateSql(table, updateFields);
			updateSql += " where RoomGUID =?";
			log.info("execute update : " + updateSql);
			updatePstmt = bizCon.prepareStatement(updateSql);

			String[] fs = updateFields.split(",");
			int columns = fs.length;
			do {
				String roomGUID = queryRs.getString("RoomGUID").trim();
				if (dataGuidMap.containsKey(roomGUID)) {
					for (int i = 1; i <= columns; i++) {
						String f = fs[i - 1].replaceAll("`", "");
						String value = queryRs.getString(f);
						updatePstmt.setString(i, value);
					}
					updatePstmt.setString(columns + 1, roomGUID);
					updatePstmt.addBatch();
					updateFlag++;
				} else {
					for (int i = 1; i <= columns; i++) {
						String f = fs[i - 1].replaceAll("`", "");
						String value = queryRs.getString(f);
						insertPstmt.setString(i, value);
					}
					insertPstmt.setTimestamp(columns + 1,
							new Timestamp(System.currentTimeMillis()));
					insertPstmt.addBatch();
					count++;
				}
				if ((count + updateFlag) % 1000 == 0) {
					insertPstmt.executeBatch();
					insertPstmt.clearBatch();
					updatePstmt.executeBatch();
					updatePstmt.clearBatch();
					log.info(count + " saved, " + updateFlag + " updated ");
				}
				try {
					pickProductOrder(queryRs, bizCon);
				} catch (Exception e) {
					e.printStackTrace();
					log.error(e);
				}
			} while (queryRs.next());
			insertPstmt.executeBatch();
			insertPstmt.clearBatch();
			updatePstmt.executeBatch();
			updatePstmt.clearBatch();

			log.info("All " + (count + updateFlag) + " rooms sync: " + count
					+ " saved, " + updateFlag + " updated ");

			saveRoomOrders(bizCon);
		} catch (Exception e) {
			e.printStackTrace();
			log.error("SYNC_ERR[ROOM]" + e);
		} finally {
			ADataSource.close(queryRs, queryPstmt, myCon);

			ADataSource.close(insertPstmt);
			ADataSource.close(updatePstmt);
			ADataSource.close(datars, dataps, bizCon);
		}
	} // ~sync

	void pickProductOrder(ResultSet rs, Connection wcon) throws SQLException {
		String guids = rs.getString("CstGUIDList");
		if (guids == null || guids.length() < 1) {
			return;
		}
		String[] guidList = guids.split("[, ;]");
		try {
			String roomGUID = rs.getString("RoomGUID");
			Set<Long> oldCstGUIDs = oldRoomCstGUIDMapping.get(roomGUID);
			if (oldCstGUIDs == null) {
				oldCstGUIDs = new HashSet<Long>();
			}
			for (String guid : guidList) {
				ProductOrder po = new ProductOrder();
				po.setCreateTime(System.currentTimeMillis() / 1000);
				po.setProductType(0); // 房产
				po.setStatus(1);
				po.setCount(1);
				po.setProductCode(roomGUID);
				long rmId = CstUtil.getCstGUIDByOriGuid(wcon, guid);
				if (rmId == 0) {
					rmId = CstUtil.retrieveMemship(guid);
				}
				if (oldCstGUIDs.contains(rmId)) {
					// 表明已经存在该订购关系，且一样，不对本条关系进行任何更新与操作
					oldCstGUIDs.remove(rmId);
					continue;
				}

				po.setCstGUID(rmId);
				po.setOriCstGUID(guid);
				orders.add(po);

			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/*
	 * 保存订购关系
	 */
	void saveRoomOrders(Connection wcon) throws SQLException {
		log.info(orders.size()
				+ " procduct-order relationships will be persisted");
		doUpdateOldRoomOrders(wcon);
		doSaveNewRoomOrders(wcon);
	}

	/*
	 * 更新现有订购关系
	 */
	void doUpdateOldRoomOrders(Connection wcon) {
		log.info("update old product order to invalid:"
				+ oldRoomCstGUIDMapping.size());
		Iterator<String> iter = oldRoomCstGUIDMapping.keySet().iterator();
		int count = 0;
		while (iter.hasNext()) {
			String roomGUID = iter.next();
			Set<Long> cstIds = oldRoomCstGUIDMapping.get(roomGUID);
			if (cstIds == null || cstIds.size() == 0) {
				continue;
			}
			productOrderLog.info("update room's cst[" + roomGUID + "]");
			for (long cstGUID : cstIds) {
				count++;
				productOrderLog.info("update " + roomGUID + " - " + cstGUID);
				String table = CstUtil.retrieveCustomerOrderTable(cstGUID);
				String sql = "update "
						+ table
						+ " set `Status`=0 "
						+ " where ProductType=0 and ProductCode=? and CstGUID=?";
				PreparedStatement pstmt = null;
				try {
					pstmt = wcon.prepareStatement(sql);
					pstmt.setString(1, roomGUID);
					pstmt.setLong(2, cstGUID);
					pstmt.executeUpdate();
				} catch (Exception e) {
					e.printStackTrace();
					productOrderLog.error("update old order error");
				} finally {
					ADataSource.close(pstmt);
				}
			} // end for
		} // end while
		log.info(count + " old orders updated");
	} //~

	/*
	 * 保存新订购关系
	 */
	void doSaveNewRoomOrders(Connection wcon) throws SQLException {
		int flag = 0;
		int size = orders.size();
		for (ProductOrder po : orders) {
			try {
				if (po.getCstGUID() == 0 && "".equals(po.getOriCstGUID())) {
					log.warn("no customer order, ignore: uid=0,origuid=");
					continue;
				}
				productOrderLog.info("save product order[Cst:"
						+ po.getCstGUID() + " - Room:" + po.getProductCode()
						+ "]");
				String table = CstUtil.retrieveCustomerOrderTable(po
						.getCstGUID());
				PreparedStatement pstmt = wcon.prepareStatement("insert into "
						+ table + "(CstGUID,CstName,ProductType,ProductCode,"
						+ "Status,OrderChangeTime,CreateTime,Comment,"
						+ "StartTime,EndTime,Count,Price,TotalMoney,"
						+ "Discount,OriCstGUID)"
						+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

				flag++;
				pstmt.setLong(1, po.getCstGUID());
				pstmt.setString(2, po.getCstName());
				pstmt.setInt(3, po.getProductType());
				pstmt.setString(4, po.getProductCode());
				pstmt.setInt(5, po.getStatus());
				pstmt.setLong(6, po.getOrderChangeTime());
				pstmt.setLong(7, System.currentTimeMillis() / 1000);// po.getCreateTime());
				pstmt.setString(8, po.getComment());
				pstmt.setLong(9, po.getStartTime());
				pstmt.setLong(10, po.getEndTime());
				pstmt.setInt(11, po.getCount());
				pstmt.setFloat(12, po.getPrice());
				pstmt.setDouble(13, po.getTotalMoney());
				pstmt.setFloat(14, po.getDiscount());
				pstmt.setString(15, po.getOriCstGUID());
				pstmt.executeUpdate();
				ADataSource.close(pstmt);
			} catch (Exception e) {
				e.printStackTrace();
				log.error(e);
			}
		}
		orders.clear();
		log.info("total " + size + " orders, " + flag + " saved, "
				+ (size - flag) + " ignored");
	} //~

	private void cacheOldOrders(String table) {
		Connection con = ADataSource.getConnection(ADataSource.SCHEMA_BIZ);
		Statement stmt = null;
		ResultSet rs = null;
		int count = 0;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery("select ProductCode,CstGUID from " + table
					+ " where ProductType=0 and `Status`=1");
			while (rs.next()) {
				String roomGUID = rs.getString(1);
				long cstGUID = rs.getLong(2);
				Set<Long> cstGUIDs = oldRoomCstGUIDMapping.get(roomGUID);
				if (cstGUIDs == null) {
					cstGUIDs = new HashSet<Long>();
					oldRoomCstGUIDMapping.put(roomGUID, cstGUIDs);
				}
				cstGUIDs.add(cstGUID);
				count++;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ADataSource.close(rs, stmt, con);
		}
		log.info(count + " product order cached.");
	}

	private List<String> loadProductOrderTables() {
		List<String> tables = new ArrayList<String>(10);
		Connection con = ADataSource.getConnection(ADataSource.SCHEMA_BIZ);
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery("show tables");
			while (rs.next()) {
				String table = rs.getString(1);
				if (table.toLowerCase().startsWith("cst_product_order_")) {
					tables.add(table);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ADataSource.close(rs, stmt, con);
		}
		return tables;
	}
}
